/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import java.util.Date;
import java.util.List;
import org.hibernate.Query;
import org.hibernate.Session;
import pojo.ChiTietHoaDon;
import pojo.NguoiDung;
import util.NewHibernateUtil;

/**
 *
 * @author 0
 */
public class ChiTietHoaDonDAO extends BaseDAO{
    public static List<ChiTietHoaDon> LayDanhSachByHoaDonID(Integer hoaDonID) throws Exception{
        Session session=NewHibernateUtil.getSessionFactory().openSession();
        List<ChiTietHoaDon> chiTietHoaDons=session.createQuery(String.format("from ChiTietHoaDon cthd where cthd.hoaDon.id=%d",hoaDonID)).list();
        session.close();
        return chiTietHoaDons;
    }
    
    public static List<ChiTietHoaDon> LayDanhSachForLapPhieuThu(Integer maKhachHang,NguoiDung nguoiDung) throws Exception{
        Session session=NewHibernateUtil.getSessionFactory().openSession();
        List<ChiTietHoaDon> chiTietHoaDons=session.createQuery(String.format("from ChiTietHoaDon cthd where (cthd.hoaDon.isDelete=false or cthd.hoaDon.isDelete=null)"
                + " and cthd.hoaDon.nguoiDung.id=%d and cthd.hoaDon.cuaHang.id=%d and cthd.daThanhToan=false",maKhachHang,nguoiDung.getCuaHang().getId())).list();
        session.close();
        return chiTietHoaDons;
    }
    
    public static List<Object[]> LayDanhSachBaoCaoDoanhThu(Date ngayFrom, Date ngayTo, Integer idCuaHang) throws Exception{
        Session session=NewHibernateUtil.getSessionFactory().openSession();
        Query query = session.createQuery(String.format("select spch.id,spch.sanPham.tenSanPham,"
                + "sum(cthd.soLuong),spch.sanPham.donGia, sum(cthd.tongTien) from SanPhamCuaHang spch join spch.chiTietHoaDons cthd with cthd.daThanhToan=true"
                + " join cthd.chiTietPhieuThus ctpt join ctpt.phieuThu pt with (pt.ngayLapPhieu>=:ngayFrom and pt.ngayLapPhieu<=:ngayTo)"
                + " where spch.cuaHang.id=%d group by spch.id,spch.sanPham.tenSanPham,spch.sanPham.donGia order by spch.id",idCuaHang));
        query.setDate("ngayFrom", ngayFrom);
        query.setDate("ngayTo", ngayTo);
        List<Object[]> objects=query.list();
        session.close();
        return objects;
    }
    
    /*public static List<Object[]> LayDanhSachBaoCaoDoanhThu(Integer idCuaHang) throws Exception{
        Session session=NewHibernateUtil.getSessionFactory().openSession();
        Query query = session.createQuery(String.format("select spch.id as maSanPhamCuaHang,spch.sanPham.tenSanPham as tenSanPham,"
                + "sum(cthd.soLuong) as soLuong,spch.sanPham.donGia as donGia, sum(cthd.tongTien) as tongTien from SanPhamCuaHang spch left join spch.chiTietHoaDons cthd with (cthd.daThanhToan=true)"
                + " where spch.cuaHang.id=%d group by spch.id,spch.sanPham.tenSanPham,spch.sanPham.donGia",idCuaHang));
        List<Object[]> objects=query.list();
        session.close();
        return objects;
    }*/
}
